package com.atguigu.edu.publisher.mapper;

import com.atguigu.edu.publisher.beans.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TradeStatsMapper {
    @Select("select province_name,\n" +
            "       sum(order_count)  order_count\n" +
            "from dws_trade_province_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by province_id, province_name;")
    List<TradeProvinceOrderCt> selectTradeProvinceOrderCt(@Param("date")Integer date);

    @Select("select province_name,\n" +
            "       sum(order_amount) order_amount\n" +
            "from dws_trade_province_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by province_id, province_name;")
    List<TradeProvinceOrderAmount> selectTradeProvinceOrderAmount(@Param("date")Integer date);

    @Select("select province_name,\n" +
            "       sum(user_count) user_count\n" +
            "from dws_trade_province_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by province_id, province_name;")
    List<TradeProvinceUserCt> selectTradeProvinceUserCt(@Param("date")Integer date);

    @Select("select source_site,\n" +
            "       sum(order_amount) order_amount\n" +
            "from dws_trade_source_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by source_id,source_site;")
    List<TradeSourceOrderAmount> selectTradeSourceAmount(@Param("date")Integer date);

    @Select("select source_site,\n" +
            "       sum(order_count)  order_count\n" +
            "from dws_trade_source_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by source_id, source_site;")
    List<TradeSourceOrderCt> selectTradeSourceOrderCt(@Param("date")Integer date);

    @Select("select source_site,\n" +
            "       sum(user_count)  user_count\n" +
            "from dws_trade_source_order_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by source_id, source_site;")
    List<TradeSourceUserCt> selectTradeSourceUserCt(@Param("date")Integer date);

    @Select("select source_site,\n" +
            "       round(round(toFloat64(order_amount), 5) /round(toFloat64(uu_cnt), 5), 20) inversionrate\n" +
            "from dws_trade_source_order_window dts\n" +
            "join dws_traffic_sc_isnew_page_window dtp\n"+
            "on dts.source_id = dtp.source_id\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by source_id,source_site;")
    List<TradeSourceInversionRate> selectTradeSourceInversionRate(@Param("date")Integer date);


}
